/******************************************************************************************************
Copyright (C) 2009 Mestrelab Research S.L. All rights reserved.

This file is part of the MNova scripting toolkit.

Authorized users of MNova Software may use this file freely, but this file is provided 
AS IS with NO WARRANTY OF ANY KIND, INCLUDING THE WARRANTY OF DESIGN, 
MERCHANTABILITY AND FITNESSFOR A PARTICULAR PURPOSE.
*****************************************************************************************************/

/*globals
    Application, mainWindow, print, DBDefinition, DBPlugin, Dir, File, MessageBox, TextStream, createDatabase
*/

// createDatabase() is defined in dbOpenCreate.qs

// Connection settings used for all test functions
var conn = { host: "localhost", port: 5504, user: "Test", password: "database" };

function dbTest() {
    "use strict";
    var db, dbdef, dbname, dbnames, dbitem, i, items, recid, hits, hit, h,
        dw = mainWindow.activeWindow();
    print(DBPlugin.toString());
    db = DBPlugin.client;
    print(db.toString());

    // Print names of databases on the server
    db.connect(conn.host, conn.port, conn.user, conn.password);
    if (!db.connected()) {
        return MessageBox.critical("Could not connect to database at {0}:{1}.".format(conn.host, conn.port));
    }
    dbnames = db.databases();
    for (i = 0; i < dbnames.length; i += 1) {
        print("  Database {0}: {1}".format(i + 1, dbnames[i]));
    }
    print(dbnames.length + " Databases.");
    db.disconnect();

    // Create a new database
    dbname = createDatabase(conn.host, conn.port, conn.user, conn.password, "test");
    if (!dbname) {
        return MessageBox.critical("Could not create database.");
    }

    // Connect to the newly created database
    db.connect(conn.host, conn.port, conn.user, conn.password, dbname);
    if (!db.connected()) {
        return MessageBox.critical("Could not connect to database {0}'.".format(dbname));
    }

    // Print info about the current database
    dbdef = db.definition(dbname);
    print("Database " + dbdef.name);
    print("   Version: " + dbdef.version);
    print("   Client Version: " + dbdef.clientVersion);
    print("   Server Version: " + dbdef.serverVersion);
    print("   Creator: " + dbdef.creator);
    print("   Created: " + dbdef.created);
    print("   Records: " + dbdef.records);

    try {
        // Save all items in document to a new record
        items = [];
        for (i = 0; i < dw.itemCount(); i += 1) {
            items.push(dw.item(i));
        }
        for (i = 0; i < items.length; i += 1) {
            print(" Item " + i + ": " + items[i]);
        }
        print("Saving...");
        recid = DBPlugin.addRecord(db, items);
        if (recid <= 0) {
            db.disconnect();
            return MessageBox.critical("Error while saving.");
        }
        print("Saved " + items.length + " items under record id " + recid + ".");
        MessageBox.information("Saved " + items.length + " items under record id " + recid + ".");

        // Retrieve saved record and paste into new document
        dw = mainWindow.newWindow();
        print("Retrieving...");
        DBPlugin.getRecord(db, recid);
        print("Pasted " + dw.itemCount() + " items from record id " + recid + ".");
        MessageBox.information("Pasted " + dw.itemCount() + " items from record id " + recid + ".");

        // Retrieve a single molecule and paste into 5th page of new document
        dw = mainWindow.newWindow();
        print("Retrieving...");
        dbitem = DBPlugin.getRecordItem(db, recid, "Molecule", 0, true, 5);
        print(dbitem);
        print("Pasted " + dw.itemCount() + " items from record id " + recid + ".");
        if (dw.itemCount() !== 1) {
            MessageBox.warning("Pasted " + dw.itemCount() + " items from record id " + recid + " but expected 1.");
        }

        // Retrieve a single spectrum and paste into 1st page of new document
        dw = mainWindow.newWindow();
        print("Retrieving...");
        dbitem = DBPlugin.getRecordItem(db, recid, "NMR Spectrum", 2, true, 1);
        print(dbitem);
        print("Pasted " + dw.itemCount() + " items from record id " + recid + ".");
        if (dw.itemCount() !== 1) {
            MessageBox.warning("Pasted " + dw.itemCount() + " items from record id " + recid + " but expected 1.");
        }

        // Do a search
        print("Searching for '-' ...");
        hits = DBPlugin.search(db, "-", "TEXT", "Molecule");
        print(hits.length  + " hits found.");
        for (h = 0; h < hits.length; h += 1) {
            hit = hits.at(h);
            print("  Hit {0}: Record={1} Item={2} Score={3} ({4})".format(hit.recordId(), hit.itemId(), hit.score(), hit.toString()));
        }

	// Do a DATETIME search
        print("Searching for items modified on 2020-11-20 ...");
        hits = DBPlugin.search(db, "= 2020-11-20", "DATETIME", "");
        print(hits.length  + " hits found.");
        for (h = 0; h < hits.length; h += 1) {
            hit = hits.at(h);
            print("  Hit {0}: Record={1} Item={2} Score={3} ({4})".format(hit.recordId(), hit.itemId(), hit.score(), hit.toString()));
        }

    } catch (e) {
        print(e);
    }

    // Disconnect
    db.disconnect();
    print("All done");
}

function dbTestInteractive() {
    "use strict";
    var db, dbdef;

    db = DBPlugin.client;
    print(db.toString());
    mainWindow.doAction("dbDatabaseOpen");
    print(db.database);
    dbdef = db.definition();
    print(dbdef);
    // Disconnect
    db.disconnect();
    print("All done");
}

function dbTestSchema() {
    "use strict";
    // Create an empty definition:
    var dbdef = DBDefinition.emptyDefinition();
    print(dbdef.toString());

    // Give it a name:
    dbdef.name = "EmptySchema";
    print(dbdef.toString());

    // The same as above in one statement:
    dbdef = DBDefinition.emptyDefinition("EmptySchema");
    print(dbdef.toString());

    // Create a default definition named "NewDatabase":
    dbdef = DBDefinition.defaultDefinition("NewDatabase");
    print(dbdef.toString());

    // Create a custom definition:
    dbdef = DBDefinition.defaultDefinition("MyDatabase");
    dbdef.removeItemType("Mnova");
    dbdef.removeField("Molecule", "Text");
    dbdef.addField("Spectrum", "myField", "TEXT", 100, "=NMRSpectrum(item).experimentType", "The experiment type of the spectrum");
    print(dbdef.toString());
}

function dbTestCreateCustomDatabase(aDatabaseNameStem) {
    "use strict";
    // Create a custom definition:
    var dbdef = DBDefinition.defaultDefinition("");
    dbdef.removeItemType("Mnova");
    dbdef.removeField("Molecule", "Text");
    dbdef.addField("Spectrum", "myField", "TEXT", 100, "=NMRSpectrum(item).experimentType", "The experiment type of the spectrum");
    // Create the database:
    createDatabase(conn.host, conn.port, conn.user, conn.password, aDatabaseNameStem, dbdef);
}

// Save all items in the active document window to the given db. 
// Returns the new record id or 0 for error.
function dbTestStore(aDB) {
    "use strict";
    print("Saving...");
    var i, recid,
        dw = mainWindow.activeWindow(),
        startTime = new Date(),
        items = [];
    for (i = 0; i < dw.itemCount(); i += 1) {
        items.push(dw.item(i));
    }
    recid = DBPlugin.addRecord(aDB, items);
    if (recid > 0) {
        print("Saved " +  items.length + " items to record " + recid + " in " + ((new Date() - startTime) / 1000) + " seconds.");
    } else {
        MessageBox.critical("Error while saving.");
    }
    return recid;
}

// Create a new database and store all items in the current document n times into it. Can be used for server stress testing.
function dbTestStoreMany() {
    "use strict";
    var logfile, log, client, dbname, i, startTime, endTime, recid,
        dw = mainWindow.activeWindow(),
        items = dw.selection(),
        n = 1000;

    logfile = new File(Dir.temp() + "/testlog.txt");
    logfile.open(File.WriteOnly);
    log = new TextStream(logfile);

    // What to save
    if (!(items.length > 0)) {
        items = dw.pageItems;
    }
    if (!(items.length > 0)) {
        MessageBox.critical("Nothing to save in current document.");
    }

    // Create a new database
    dbname = createDatabase(conn.host, conn.port, conn.user, conn.password, "C" + n);
    if (dbname === undefined) {
        return MessageBox.critical("Could not create database.");
    }

    // Connect to the newly created database
    client = DBPlugin.createClient();
    client.connect(conn.host, conn.port, conn.user, conn.password, dbname);
    if (!client.connected()) {
        return MessageBox.critical("Could not connect to database '" + dbname + "'.");
    }

    // Save n records
    try {
        startTime = new Date();
        for (i = 1; i <= n; i += 1) {
            print("Saving record " + i + "... ");
            log.write("Saving record " + i + "... ");
            log.flush();
            // Send to db as new record - binary page ids are internally generated from recordid:methodid:itemid
            recid = client.saveRecord(0, items);
            if (recid <= 0) {
                client.disconnect();
                return MessageBox.critical("Error while saving.");
            }
            log.writeln("Saved " + dw.selection().length + " items under record id " + recid + ".");
        }
        endTime = new Date();
        print("Saved " + n + " records in " + ((endTime - startTime) / 1000) + " seconds.");
    } catch (e) {
        print(e);
    }
    // Disconnect
    client.disconnect();
    logfile.close();
    print("All done.");
}

function dbTestDelete() {
    "use strict";
    var
        dbname = "TestDelete",
        client = DBPlugin.createClient();
    client.connect(conn.host, conn.port, conn.user, conn.password, dbname);
    if (!client.connected()) {
        return MessageBox.critical("Could not connect to database '" + dbname + "'.");
    }
    if (client.database !== dbname) {
        return MessageBox.critical("Could not find database '" + dbname + "'.");
    }

    function testDeleteRecords(record_spec) {
        var result;
        print("Deleting record(s) '" + record_spec + "'");
        result = client.deleteRecords(record_spec);
        print("    Result = " + result);
    }

    function testDeleteItems(record_spec, method_spec, item_spec) {
        print("Deleting items '" + item_spec + "' of type '" + method_spec + "' from record(s) '" + record_spec + "'");
        var result = client.deleteItems(record_spec, method_spec, item_spec);
        print("    Result = " + result);
    }

    testDeleteRecords(2);
    testDeleteRecords("3-5,7");
    testDeleteItems(1, "Molecule", 0); // Item number is always 0 for molecules
    testDeleteItems(5, "NMR Spectrum",  "1,2");
    testDeleteItems("6", "NMR Spectrum", "");  // Deletes nothing
    testDeleteItems(8, "NMR Spectrum", "1-10");
    testDeleteItems(9, "NMR Spectrum", "0");   // Deletes all NMR spectra from record 9
}

function dbTestReportSearchResults() {
    "use strict";
    var hits, hit, h, tableText,
        db = DBPlugin.client;
    mainWindow.doAction("dbDatabaseOpen");
    print(db.database);

    // Do a search
    print("Searching for '-' ...");
    hits = DBPlugin.search(db, "-", "TEXT", "Molecule");
    print(hits.length  + " hits found.");

    // Format hits as html table
    if (hits.length > 0) {
        tableText = '<font style="font-size: 8pt; font-family: Arial; color: black">';
        tableText += '<html><head>';
        tableText += '<title>DB Hits</title>';
        tableText += '</head><body>';
        tableText += '\n<table border="1" cellSpacing="0" cellPadding="4" width="100%">';
        tableText += '\n<tr style="background-color:silver">';
        tableText += '\n  <td><b>Hit</b></td>';
        tableText += '\n  <td><b>Record</b></td>';
        tableText += '\n  <td><b>Item</b></td>';
        tableText += '\n  <td><b>Score</b></td>';
        tableText += '\n  <td><b>Url</b></td>';
        tableText += '</tr>';
        for (h = 0; h < Math.min(hits.length, 30); h += 1) {
            hit = hits.at(h);
            print(" Hit " + h + ": Record=" + hit.recordId() + " Item=" + hit.itemId() + " Score=" + hit.score() + " (" + hit.toString() + ")");
            tableText += '\n<tr>';
            tableText += '\n  <td>' + (h + 1) + '</td>';
            tableText += '\n  <td>' + hit.recordId() + '</td>';
            tableText += '\n  <td>' + hit.itemId() + '</td>';
            tableText += '\n  <td>' + hit.score() + '</td>';
            tableText += '\n  <td>' + hit.toString() + '</td>';
            tableText += '\n</tr>';
        }
        tableText += '</table>';
        tableText += '</body></html>';
        // Draw the html table on current page
        Application.draw.text(tableText, "Report", "DB Hits", true);
    }
}

